from django.shortcuts import render
from django.http import HttpResponse , HttpResponseRedirect
from bookstore.models import *
from django.db.models import *


# Create your views here.

#数据库相关的测试
def book_test(request):

    #======all()方法，查询Book实体类中所有的数据=====
    #等同与select * from table
    #返回值：   QuerySet容器对象,内部存放BookModel实例
    #等同于select * from book
    # books = Book.objects.all()
    # for book in books:
    #     print("书名:" , book.title , "出版社:" , book.pub)
    # return HttpResponse(books)

    #==========查询部分列的数据返回========
    #等同与select title , pub , price from book
    #返回值 QuerySet ： 返回查询结果容器,容器内存字典,每个字典代表一条数据
    # books = Book.objects.values('title' , 'pub' , 'price')
    # for book in books:
    #     print(book['title'])
    # return HttpResponse(books)

    #====values_list('列1' , '列2')
    #作用，返回元组形式的查询结果
    #等同与  select 列1,列2 from xxx
    #会将查询出来的结果封装到元祖中,再封装到Q查询集合querySet中
    # books = Book.objects.values_list('title' , 'price')
    # return HttpResponse(books)

    #order_by() 排序
    #与all()方法不同,它会用sql语句的order by 子句对查询结果进行根据某个字段
    #选择性的排序
    #默认是按照升序排序,降序排序则需要再列前面加 -
    #这个其实原理很简单,当再调用order_by方法的时候 根据里面的字段是否有 - 判断当前是升序还是降序
    # books = Book.objects.order_by('-price')
    # books_query = books.query
    # return HttpResponse(books_query)

    #查询书中出版社为"清华大学出版社"的图书
    # books = Book.objects.filter(pub="清华大学出版社" , title="Python")

    #查询Author实体类中name为王老师并且age是28岁的
    # authors = Author.objects.filter(name="王老师" , age = 28)
    # return HttpResponse(books)

    #====exclude(条件)======
    #作用:返回不包含此条件的全部数据集
    #示例 : 查询清华大学出版社,定价等于50以外的全部图书
    # books = Book.objects.exclude(pub="清华大学出版社" , price=50)
    # return HttpResponse(books)


    #===get(条件)=====
    #语法 MyModel.objects.get(条件)
    #作用:返回满足条件的唯一一条数据
    #说明：该方法只能返回一条数据
    #查询结果多余一条数据则抛出 Model.MultipleObjectsReturned异常
    #查询结果如果没有数据则抛出Models.DoesNotExist异常
    # book = Book.objects.get(pub="清华大学出版社")
    # book = Book.objects.get(id=1)
    # return HttpResponse(book)

    #不支持下面这种写法
    # book = Book.objects.get(id>1)
    # return HttpResponse(book)

    #定义：做更灵活的条件查询时候需要使用到查询谓词
    #说明:每一个查询谓词是一个独立的查询功能
    #__exact:等值匹配
    # author = Author.objects.filter(id__exact=1)
    # return HttpResponse(author)

    #__contains : 包含指定值 【其实就是模糊匹配】
    # authors = Author.objects.filter(email__contains='w')
    # authors = Author.objects.filter(email__startswith='w')
    # print(authors.query)
    # return HttpResponse(authors)

    #__gt  : 大于指定值
    #等同与 select * from author where age > 50
    # __gte : 大于等于
    # __lt:小于
    # __lte : 小于等于
    # authors = Author.objects.filter(age__gt=50)
    # print(authors.query)
    # return HttpResponse(authors)

    #__in:查询数据是否在指定范围内
    #SELECT `author`.`id`, `author`.`name`, `author`.`age`, `author`.`email` FROM `author` WHERE `author`.`age` IN (30, 31)
    # authors = Author.objects.filter(age__in=[30 , 31])
    # print(authors.query)
    # return HttpResponse(authors)

    #__range:查找数据是否在指定的区间范围内
    #查找年龄在某一区间内的所有作者
    #SELECT `author`.`id`, `author`.`name`, `author`.`age`, `author`.`email` FROM `author` WHERE `author`.`age` BETWEEN 30 AND 35
    authors = Author.objects.filter(age__range=(30 , 35))
    print(authors.query)
    return HttpResponse(authors)


#修改单条书籍信息
def save_book(request):
    """
    更新单个实体的某些字段值的步骤
    (1).查
        通过get()得到要修改的实体对象
    (2).改
        通过对象.属性的方式修改数据
    (3).保存
        通过对象.save()保存数据
    """
    #单个修改
    # b1 = Book.objects.get(id=1)
    # b1.price = 23
    # b1.save()

    #直接调用QuerySet的update(属性=值)实现批量修改
    #将id大于3的所有图书价格定为0元
    books = Book.objects.filter(id__gt=3)
    books.update(price = 0)

    #将所有书的零售价定为100元
    books = Book.objects.all()
    books.update(market_price = 100)

    return HttpResponse('book')


#查看所有的书籍
def all_books(request):
    books = Book.objects.filter(is_active=1)
    return render(request , "bookstore/all_books.html" , locals())

#更新书籍
def update_book(request , book_id):
    #查找书籍的信息
    try:
        book = Book.objects.get(id=book_id)
    except Exception as e:
        return HttpResponse("书本信息不存在")

    #如果当前是GET,就获取编辑书籍的页面
    if request.method == 'GET':
        return render(request , "bookstore/update_book.html" , locals())
    elif request.method == 'POST':
        #商品单价
        price = request.POST['price']
        #商品零售价
        market_price = request.POST['market_price']
        #修改
        book.price = price
        book.market_price = market_price
        #保存
        book.save()
        #进行页面书籍的修改操作
        return HttpResponseRedirect("/book/all_book")



#删除书籍信息
def delete_book(request):
    if request.method == 'GET':
        book_id = request.GET.get('book_id')
        try:
            book = Book.objects.get(id = book_id)
            book.is_active = 0
            book.save()
            return HttpResponseRedirect("/book/all_book")
        except Exception as e:
            return HttpResponse("要删除的书籍信息不存在")


#F对象和Q对象
def fqdo(request):
    #示例1，更新Book实例中所有零售价涨10元
    #可以看出来,F对象的效率是真的很高的
    # Book.objects.all().update(market_price = F('market_price') + 10)
    #update bookstore_book set market_price = market_price + 10
    #以上做法就相当于是下面的代码 , 可以测试出来，真的很慢
    # books = Book.objects.all()
    # for book in books:
    #     # book.market_price = book.market_price + 10
    #     book.market_price = F('market_price') + 10
    #     book.save()



    #示例2.对数据库中两个字段进行比较，列出哪些书的零售价高于定价
    # books = Book.objects.filter(market_price__gt=F('price'))
    # # print(books.query)
    # # print(books)
    #
    # for book in  books:
    #     print(f"{book.title} 定价:{book.price} , 现价 :{book.market_price}")

   #查找清华大学出版社的书或者价格低于50的书
    # books = Book.objects.filter(Q(market_price__lt = 50) | Q(pub__exact='清华大学出版社'))
    # print(books.query)

    #查找不是机械工业出版社的书并且价格低于50的书
    books = Book.objects.filter(Q(market_price__lt = 50) & ~Q(pub__exact='机械工业出版社'))
    print(books.query)

    return HttpResponse(books)

#查询所有的游戏
def all_games(request):
    games = Game.objects.all()
    return HttpResponse(games)

#查看小帮菜的所有短信
def all_sms(request):
    sms = TSms.objects.all()
    return HttpResponse(sms)

#聚合查询
def poly_select(request):
    #聚合查询  查询出总数
    # ress = Book.objects.aggregate(res = Count('id'))
    # return HttpResponse(ress['res'])

    #分组聚合查询  查询出每一项的数量
    # bd = Book.objects.values('pub')
    # #为什么解析出来是这样的 这是人家做了精心设计 所以 咱遵守即可
    # res = bd.annotate(res_count=Count('id')).filter(res_count__gt=1)
    # print(res.query)

    #支持原生操作  返回 RawQuerySet集合对象【只支持基础操作,比如循环】
    #因为已经到了原生了
    # books = Book.objects.raw('select *from book')
    # for book in books:
    #     print(book)

    #本来只想查询出来一个 结果最后全部查询出来了 这就达到了sql注入的结果
    #sql注入范式   错误
    #果然 额 现在   1.没  2有休息的 看自己 自己 自己稀罕  自己喜欢就按就好哦啊

    # books = Book.objects.raw('select * from book where id = %s'%('1 or 1 = 1'))
    #正确的做法
    #其实这里只查询出了 1  剩下的就忽略了
    books = Book.objects.raw('select * from book where id = %s' , ['1 or i = 1'])
    for book in books:
        print(book)

    return HttpResponse("ok")

#原生数据库操作 - cursor
def cursor_do(request):
    #使用sql语句将id=8的书的出版社改为 "南开大学出版社"
    from django.db import connection
    # with connection.cursor() as cur:
    #     cur.execute("update book set pub = '南开大学出版社' where id = 8")

    #删除一条记录
    with connection.cursor() as cur:
        #删除id为7的记录
        cur.execute('delete from book where id = 7')

    return HttpResponse("ok")



